--    Program: 		CAPARIMP_RECEIPT_APPLICATION.sql
--    Description:  Import AR RECEIPT APPLICATION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   lv_FileName		VARCHAR2(100)	:=	'logc.log';
   lv_PathName		VARCHAR2(100)	:=	'/tmp';
   lv_UTL_File		UTL_FILE.File_Type;
   lv_counter		VARCHAR(100);
   
   l_receivable_application_id	number;
                    		                 		
   cursor cur_ar_receipt_application is
      select * from CRST_AR_RECEIVABLE_APPLICATION
     -- WHERE RECEIVABLE_APPLICATION_ID = 28175
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   lv_counter := 0;
   
   for cur in cur_ar_receipt_application loop   	
     l_err_msg_tmp := '';
     
     lv_counter := lv_counter + 1;
     
     lv_UTL_File := UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
     UTL_FILE.PUT_LINE(lv_UTL_File, lv_counter);
	 UTL_FILE.FClose(lv_UTL_File);
	 
     SELECT AR_RECEIVABLE_APPLICATIONS_S.NEXTVAL 
     INTO l_receivable_application_id
     FROM DUAL;
     
     update crst_ar_receivable_application
     set    receivable_application_id_n = l_receivable_application_id
     where  receivable_application_id = cur.RECEIVABLE_APPLICATION_ID;
     
     begin
       -- import data
       insert into AR_RECEIVABLE_APPLICATIONS_ALL
       (
          RECEIVABLE_APPLICATION_ID       ,
          LAST_UPDATED_BY                 ,
          LAST_UPDATE_DATE                ,
          CREATED_BY                      ,
          CREATION_DATE                   ,
          AMOUNT_APPLIED                  ,
          AMOUNT_APPLIED_FROM             ,
          TRANS_TO_RECEIPT_RATE           ,
          GL_DATE                         ,
          CODE_COMBINATION_ID             ,
          SET_OF_BOOKS_ID                 ,
          DISPLAY                         ,
          APPLY_DATE                      ,
          APPLICATION_TYPE                ,
          STATUS                          ,
          PAYMENT_SCHEDULE_ID             ,
          LAST_UPDATE_LOGIN               ,
          CASH_RECEIPT_ID                 ,
          APPLIED_CUSTOMER_TRX_ID         ,
          APPLIED_CUSTOMER_TRX_LINE_ID    ,
          APPLIED_PAYMENT_SCHEDULE_ID     ,
          CUSTOMER_TRX_ID                 ,
          LINE_APPLIED                    ,
          TAX_APPLIED                     ,
          FREIGHT_APPLIED                 ,
          RECEIVABLES_CHARGES_APPLIED     ,
          EARNED_DISCOUNT_TAKEN           ,
          UNEARNED_DISCOUNT_TAKEN         ,
          DAYS_LATE                       ,
          APPLICATION_RULE                ,
          GL_POSTED_DATE                  ,
          COMMENTS                        ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          POSTABLE                        ,
          POSTING_CONTROL_ID              ,
          ACCTD_AMOUNT_APPLIED_FROM       ,
          ACCTD_AMOUNT_APPLIED_TO         ,
          ACCTD_EARNED_DISCOUNT_TAKEN     ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          CONFIRMED_FLAG                  ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          REQUEST_ID                      ,
          USSGL_TRANSACTION_CODE          ,
          USSGL_TRANSACTION_CODE_CONTEXT  ,
          EARNED_DISCOUNT_CCID            ,
          UNEARNED_DISCOUNT_CCID          ,
          ACCTD_UNEARNED_DISCOUNT_TAKEN   ,
          REVERSAL_GL_DATE                ,
          CASH_RECEIPT_HISTORY_ID         ,
          ORG_ID                          ,
          TAX_CODE                        ,
          GLOBAL_ATTRIBUTE1               ,
          GLOBAL_ATTRIBUTE2               ,
          GLOBAL_ATTRIBUTE3               ,
          GLOBAL_ATTRIBUTE4               ,
          GLOBAL_ATTRIBUTE5               ,
          GLOBAL_ATTRIBUTE6               ,
          GLOBAL_ATTRIBUTE7               ,
          GLOBAL_ATTRIBUTE8               ,
          GLOBAL_ATTRIBUTE9               ,
          GLOBAL_ATTRIBUTE10              ,
          GLOBAL_ATTRIBUTE11              ,
          GLOBAL_ATTRIBUTE12              ,
          GLOBAL_ATTRIBUTE13              ,
          GLOBAL_ATTRIBUTE14              ,
          GLOBAL_ATTRIBUTE15              ,
          GLOBAL_ATTRIBUTE16              ,
          GLOBAL_ATTRIBUTE17              ,
          GLOBAL_ATTRIBUTE18              ,
          GLOBAL_ATTRIBUTE19              ,
          GLOBAL_ATTRIBUTE20              ,
          GLOBAL_ATTRIBUTE_CATEGORY       ,
          CONS_INV_ID                     ,
          CONS_INV_ID_TO                  ,
          RULE_SET_ID                     ,
          LINE_EDISCOUNTED                ,
          TAX_EDISCOUNTED                 ,
          FREIGHT_EDISCOUNTED             ,
          CHARGES_EDISCOUNTED             ,
          LINE_UEDISCOUNTED               ,
          TAX_UEDISCOUNTED                ,
          FREIGHT_UEDISCOUNTED            ,
          CHARGES_UEDISCOUNTED            ,
          RECEIVABLES_TRX_ID              ,
          ON_ACCOUNT_CUSTOMER             ,
          MRC_AMOUNT_APPLIED              ,
          MRC_AMOUNT_APPLIED_FROM         ,
          MRC_DISPLAY                     ,
          MRC_STATUS                      ,
          MRC_PAYMENT_SCHEDULE_ID         ,
          MRC_CASH_RECEIPT_ID             ,
          MRC_GL_POSTED_DATE              ,
          MRC_POSTING_CONTROL_ID          ,
          MRC_ACCTD_AMOUNT_APPLIED_FROM   ,
          MRC_ACCTD_AMOUNT_APPLIED_TO     ,
          MRC_ACCTD_EARNED_DISC_TAKEN     ,
          MRC_ACCTD_UNEARNED_DISC_TAKEN   ,
          EDISC_TAX_ACCT_RULE             ,
          UNEDISC_TAX_ACCT_RULE           ,
          LINK_TO_TRX_HIST_ID             ,
          LINK_TO_CUSTOMER_TRX_ID         ,
          APPLICATION_REF_TYPE            ,
          APPLICATION_REF_ID              ,
          APPLICATION_REF_NUM             ,
          CHARGEBACK_CUSTOMER_TRX_ID      ,
          SECONDARY_APPLICATION_REF_ID    ,
          PAYMENT_SET_ID                  ,
          APPLICATION_REF_REASON          ,
          CUSTOMER_REFERENCE               
       )
       values
       (
            l_receivable_application_id         ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_DATE                ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.CREATION_DATE                   ,
            cur.AMOUNT_APPLIED                  ,
            cur.AMOUNT_APPLIED_FROM             , --NULL
            cur.TRANS_TO_RECEIPT_RATE           ,
            cur.GL_DATE                         ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID),
            CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
            cur.DISPLAY                         ,
            cur.APPLY_DATE                      ,
            cur.APPLICATION_TYPE                ,
            cur.STATUS                          ,
            CAPARIMP_MAPPING.GetPaymentScheduleIDMapping(cur.PAYMENT_SCHEDULE_ID),
            cur.LAST_UPDATE_LOGIN               ,
            CAPARIMP_MAPPING.GetCashReceiptIDMapping(cur.CASH_RECEIPT_ID),
            CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.APPLIED_CUSTOMER_TRX_ID),
            CAPARIMP_MAPPING.GetCustomerTrxLineIDMapping(cur.APPLIED_CUSTOMER_TRX_LINE_ID),
            CAPARIMP_MAPPING.GetPaymentScheduleIDMapping(cur.APPLIED_PAYMENT_SCHEDULE_ID),
            CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.CUSTOMER_TRX_ID),
            cur.LINE_APPLIED,
            cur.TAX_APPLIED                     ,
            cur.FREIGHT_APPLIED                 ,
            cur.RECEIVABLES_CHARGES_APPLIED     ,
            cur.EARNED_DISCOUNT_TAKEN           ,
            cur.UNEARNED_DISCOUNT_TAKEN         ,
            cur.DAYS_LATE                       ,
            cur.APPLICATION_RULE                ,
            cur.GL_POSTED_DATE                  ,
            cur.COMMENTS                        ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.POSTABLE                        ,
            cur.POSTING_CONTROL_ID              ,
            cur.ACCTD_AMOUNT_APPLIED_FROM       ,
            cur.ACCTD_AMOUNT_APPLIED_TO         ,
            cur.ACCTD_EARNED_DISCOUNT_TAKEN     ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.CONFIRMED_FLAG                  ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.REQUEST_ID                      ,
            cur.USSGL_TRANSACTION_CODE          ,
            cur.USSGL_TRANSACTION_CODE_CONTEXT  ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.EARNED_DISCOUNT_CCID),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.UNEARNED_DISCOUNT_CCID),
            cur.ACCTD_UNEARNED_DISCOUNT_TAKEN   ,
            cur.REVERSAL_GL_DATE                ,
            CAPARIMP_MAPPING.GetCashReceiptHistoryIDMapping(cur.CASH_RECEIPT_HISTORY_ID),
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.TAX_CODE                        ,
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.CONS_INV_ID                     ,
            cur.CONS_INV_ID_TO                  ,
            cur.RULE_SET_ID                     ,
            cur.LINE_EDISCOUNTED                ,
            cur.TAX_EDISCOUNTED                 ,
            cur.FREIGHT_EDISCOUNTED             ,
            cur.CHARGES_EDISCOUNTED             ,
            cur.LINE_UEDISCOUNTED               ,
            cur.TAX_UEDISCOUNTED                ,
            cur.FREIGHT_UEDISCOUNTED            ,
            cur.CHARGES_UEDISCOUNTED            ,
            cur.RECEIVABLES_TRX_ID              ,
            cur.ON_ACCOUNT_CUSTOMER             ,
            cur.MRC_AMOUNT_APPLIED              ,
            cur.MRC_AMOUNT_APPLIED_FROM         ,
            cur.MRC_DISPLAY                     ,
            cur.MRC_STATUS                      ,
            cur.MRC_PAYMENT_SCHEDULE_ID         ,
            cur.MRC_CASH_RECEIPT_ID             ,
            cur.MRC_GL_POSTED_DATE              ,
            cur.MRC_POSTING_CONTROL_ID          ,
            cur.MRC_ACCTD_AMOUNT_APPLIED_FROM   ,
            cur.MRC_ACCTD_AMOUNT_APPLIED_TO     ,
            cur.MRC_ACCTD_EARNED_DISC_TAKEN     ,
            cur.MRC_ACCTD_UNEARNED_DISC_TAKEN   ,
            cur.EDISC_TAX_ACCT_RULE             ,
            cur.UNEDISC_TAX_ACCT_RULE           ,
            cur.LINK_TO_TRX_HIST_ID             ,
            cur.LINK_TO_CUSTOMER_TRX_ID         ,
            cur.APPLICATION_REF_TYPE            ,
            cur.APPLICATION_REF_ID              ,
            cur.APPLICATION_REF_NUM             ,
            cur.CHARGEBACK_CUSTOMER_TRX_ID      ,
            cur.SECONDARY_APPLICATION_REF_ID    ,
            cur.PAYMENT_SET_ID                  ,
            cur.APPLICATION_REF_REASON          ,
            cur.CUSTOMER_REFERENCE                                   
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_RECEIPT_APPICATION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
